package com.bojx.report.dao;

import java.util.List;

import com.jfinal.kit.Kv;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.SqlPara;

public class QueryDao {
	public List<Record> findBankBranchData(String bankName,int year ,int month){
		Kv cond = Kv.by("year", year).set("month", month).set("organization", bankName);
		SqlPara sqlPara = Db.getSqlPara("query.findBankBranchData", cond);
		return Db.find(sqlPara);
	}

	public List<Record> findClientData(String bankName, int year, int month) {
		Kv cond = Kv.by("year", year).set("month", month).set("organization", bankName);
		SqlPara sqlPara = Db.getSqlPara("query.findClientData", cond);
		return Db.find(sqlPara);
	}
	
	public List<String> findAreaData(){
		return  Db.query("select area from report_area_data");
	}

	public Record queryBusinessData(String area,String bankName, int year, int month, String billType, String actionType, String statisticsType) {
		Kv cond = Kv.by("area",area).set("year", year).set("month", month).set("organization", bankName).set("bill_type",billType).set("action_type",actionType).set("statistics_type",statisticsType);
		SqlPara sqlPara = Db.getSqlPara("query.queryBusinessData", cond);
		return Db.findFirst(sqlPara);
	}
	
	public Page<Record> queryExcelData(Kv con){
		SqlPara sqlPara=Db.getSqlPara("query.queryExcelData",con);
		int pageNumber=con.getInt("pageNum");
		int pageSize=con.getInt("rows");
		return Db.paginate(pageNumber, pageSize, sqlPara);
	}
	
	/**
	 * 查询客户情况
	 * 
	 * 
	 */
	public Record queryClientInfo(String bank,String area,int year,int month){
		Kv con=Kv.by("area", area).set("organization", bank)
				.set("year", year).set("month", month);
		SqlPara sqlPara=Db.getSqlPara("query.queryClientInfo", con);
		return Db.findFirst(sqlPara);
	}
	
	/***
	 * 
	 * 查询网点情况
	 */
	public Record queryBranchInfo(String bank,String area,int year,int month){
		Kv con=Kv.by("area", area).set("organization", bank)
				.set("year", year).set("month", month);
		SqlPara sqlPara=Db.getSqlPara("query.queryBranchInfo", con);
		return Db.findFirst(sqlPara);
	}
	
	/**
	 * 
	 * 查询业务量
	 * 
	 */
	
	public Record queryBusiness(Kv con){
		SqlPara sqlPara=Db.getSqlPara("query.queryBusiness", con);
		return Db.findFirst(sqlPara);
	}
//------------------------------------------------------------------------------
	public Page<Record> queryQuarterExcelData(Kv con) {
		SqlPara sqlPara=Db.getSqlPara("query.queryQuarterExcelData",con);
		int pageNumber=con.getInt("pageNum");
		int pageSize=con.getInt("rows");
		return Db.paginate(pageNumber, pageSize, sqlPara);
	}
	
	/***
	 * 查询银行地区网点情况
	 */
	public Record findQuarterBranchInfo(String bank,String area,int year,int quarter){
		Kv con=Kv.by("bank", bank).set("area", area).set("year", year).set("quarter", quarter);
		SqlPara para=Db.getSqlPara("query.findQuarterBranchInfo", con);
		return Db.findFirst(para);
	}
	
	/***
	 * 查询客户情况
	 */
	public Record findQuarterClientInfo(String bank,String area,int year,int quarter){
		Kv con=Kv.by("bank", bank).set("area", area).set("year", year).set("quarter", quarter);
		SqlPara para=Db.getSqlPara("query.findQuarterClientInfo", con);
		return Db.findFirst(para);
	}
	
	/***
	 * 查询业务量
	 */
	
	public Record findQuarterBusinessData(String bank,String area,int year,int quarter,String billType,int scope){
		Kv con=Kv.by("bank", bank).set("area", area).set("year", year)
				.set("quarter", quarter).set("bill_type", billType).set("scope", scope);
		SqlPara para=Db.getSqlPara("query.findQuarterBusinessData", con);
		return Db.findFirst(para);
	}

	public Record findUploadExcel(String bank, int year, int month) {
		Kv con=Kv.by("bank", bank).set("year", year).set("month", month);
		SqlPara para=Db.getSqlPara("query.findUploadExcel", con);
		return Db.findFirst(para);
	}
}
